package com.liuqing.www.数据库介绍;

import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Iterator;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

public class Java增删改查操作 {
	public static void main(String[] args) {
		
	}
	/**
	 * 批量执行sql语句（删、更新）
	 * @param sqlList
	 * @param conn
	 * @return
	 */
	public static int[] updateList(List<String>sqlList,Connection conn){
		   int result[] = null;
           Statement stmt =null;
           try {
                   conn.setAutoCommit(false);
                   stmt= conn.createStatement();
                   int size = sqlList.size();
                   result = new int[size];
                   if (size > 0) {
                           for (int i = 0; i < size; i++) {
                                   String sqlStr = (String) sqlList.get(i);
                                   stmt.addBatch(sqlStr);
                           }
                           int[] rows = stmt.executeBatch();
                           int len = rows.length;
                           for (int j = 0; j < len; j++) {
                                   int rowCount = rows[j];
                                   if (rowCount <= 0) {
//                                           LogWritter.bizDetailInfo("第(" + j + ")个sql执行不成功");
                                           result[j] = rows[j];
                                   }
                           }
                           result=rows;
                   }
                   conn.commit();
           } catch (SQLException ex) {
               result = null;
                   try {
                   	conn.rollback();
                       conn.close();
                   } catch (SQLException ex1) {
                   }
                   ex.printStackTrace();
           }finally{
               try {
                   if(stmt!=null){
                       stmt.close();
                   }
//                   if(conn!=null){
//                       conn.close();
//                   }
               } catch (SQLException ex2) {
               }
           }
           return result;
   }
	/**
	 * sqlTemplet为 UPDATE table4 SET m = ? WHERE x = ?"，从1开始
	 * @param conn
	 * @param sqlParms
	 * @param sqlTemplet
	 * @return
	 */
	static int[] executeTemplet(Connection conn,List<Object>sqlParms,String sqlTemplet){
		   int[] result = null;
           PreparedStatement prepareStatement = null;
           try {
        	   PreparedStatement ps = conn.prepareStatement(sqlTemplet);
               // 无参数 返回PreparedStatement
               if (sqlParms == null || sqlParms.size() == 0) {
                       ps.addBatch();
               }else{

               // 递归设置PreparedStatement的参数

               for (Iterator<Object> paramListI = sqlParms.iterator(); paramListI.hasNext();) {
                       // 每个SQL的参数组
                       Object paramsObj = paramListI.next();
                       if (paramsObj == null) {
                               ps.addBatch();
                               continue;
                       }

                       Iterator<Object> sqlParmsI = ((List<Object>) paramsObj).iterator();
                       for (int j = 1; sqlParmsI.hasNext(); j++) {
                               // 每个SQL的参数

                               Object paramObj = sqlParmsI.next();
                               // 根据型别使用不同的数据

                               if (paramObj == null) {
                                       ps.setNull(j, Types.CHAR);
                               } else if (paramObj instanceof String) {
                                       ps.setString(j, (String) paramObj);
                               } else if (paramObj instanceof Long) {
                                       ps.setLong(j, ((Long) paramObj).longValue());
                               } else if (paramObj instanceof Integer) {
                                       ps.setInt(j, ((Integer) paramObj).intValue());
                               } else if (paramObj instanceof Double) {
                                       ps.setDouble(j, ((Double) paramObj).doubleValue());
                               } else if (paramObj instanceof Date) {
                                       ps.setDate(j, ((Date) paramObj));
                               } else if (paramObj instanceof Boolean) {
                                       ps.setBoolean(j, ((Boolean) paramObj).booleanValue());
                               } else if (paramObj instanceof Short) {
                                       ps.setShort(j, ((Short) paramObj).shortValue());
                               } else if (paramObj instanceof Time) {
                                       ps.setTime(j, ((Time) paramObj));
                               } else if (paramObj instanceof Timestamp) {
                                       ps.setTimestamp(j, ((Timestamp) paramObj));
                               } else if (paramObj instanceof Float) {
                                       ps.setFloat(j, ((Float) paramObj).floatValue());
                               } else if (paramObj instanceof Blob) {
                                       ps.setBlob(j, ((Blob) paramObj));
                               } else if (paramObj instanceof Clob) {
                                       ps.setClob(j, ((Clob) paramObj));
                               } else {
                                       ps.setObject(j, paramObj);
                               }
                       }
                       ps.addBatch();
                       result = prepareStatement.executeBatch();
               }
               }    
           } catch (SQLException ex) {
                   result = null;
                   ex.printStackTrace();
           } finally {
               prepareStatement=null;
//                if(prepareStatement!=null){
//                    try {
//                        prepareStatement.close();
//                    } catch (SQLException ex) {
//                        LogWritter.sysError(ex.getMessage(), ex);
//                        result = null;
//                        ex.printStackTrace();
//                    }
//                }
           }
           return result;
	}
/**
 * 插入生成返回自动生成的主键
 * @param con
 * @param sql
 * @return
 */
	static long getNextID(Connection con,String sql){
		long ret = -1 ;
		
        ResultSet rs = null ;
        PreparedStatement state = null ;
        try {
        	state = con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            int t = state.executeUpdate();
//            System.out.println("return rows:"+t);
            if(t > 0){
            	rs = state.getGeneratedKeys();
                if (rs.next()) {
                    ret =  rs.getLong(1);
                }
            }
        } catch (SQLException e) {
    		try {
    			if(rs != null){
    				rs.close();
    			}
    			if(state != null){
    				state.close();
    			}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
        	e.printStackTrace();
        }finally{
        	try {
    			if(rs != null){
    				rs.close();
    			}
    			if(state != null){
    				state.close();
    			}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
        }
		return ret ;
	}
	//分页操作，指定要取的分页起始页和结束页及要查询的sql语句

    public static CachedRowSet findAllPrePage(Connection con, String strSql,
                                       int firstResult,int maxResults) {
        String presql=getPaginationSql(strSql,firstResult,maxResults);
       // rs = ps.executeQuery(); //执行查询操作
        return null;
       // return findAll(con,presql,null);
    }
    private static String getPaginationSql(String sql,int firstResult,int maxResults){
            StringBuffer paginationSql=new StringBuffer("");
            paginationSql.append("select * from ( select row_.*, rownum rownum_ from ( ");
            paginationSql.append(sql);
            paginationSql.append(" ) row_ where rownum < ").append(firstResult+maxResults);
            paginationSql.append(") where rownum_ >= ").append(firstResult);
            
            return paginationSql.toString();
	}
}

